import msg
import MySQLdb
import urllib
import gtk

clipboard = gtk.clipboard_get()

def query(sql):
	conn = MySQLdb.connect(host='localhost', user='root', passwd='', db='defcon')
	conn.query(sql)
	rs = conn.store_result().fetch_row(maxrows=10)
	conn.close()
	return rs

def get(what, value, by='id'):
	conn = MySQLdb.connect(host='localhost', user='root', passwd='', db='defcon')
	if type(by) == list:
		where = []
		for field in by:
			where.append('{0} = {1}'.format(field, value))
		sql = 'SELECT * FROM {0} WHERE {1}'.format(what, ' OR '.join(where))
	else:
		sql = 'SELECT * FROM {0} WHERE {1} = {2}'.format(what, by, value)
	try:
		conn.query(sql)
	except MySQLdb.Error as (errnum, errstr):
		msg.show('ERROR {0}: {1}'.format(errnum, errstr), 'red', 1)
		msg.show(sql, 'red', 1)
		conn.close()
		return
	hits = conn.store_result().fetch_row(maxrows=0)
	conn.close()
	return hits

def lookup(ocean, bb):
	x = ocean[0:1]; y = ocean[1:2]
	
	sql = 'SELECT c.town_id, DATE_FORMAT(c.time, "%M %D, %H:%i"), c.new_player_id, c.old_player_id, c.new_ally_id, c.old_ally_id, c.town_points '
	sql = sql + 'FROM conquers c LEFT JOIN towns t ON c.town_id = t.id '
	sql = sql + 'WHERE t.island_x > {0}00 and t.island_x < {0}99 and t.island_y > {1}00 and t.island_y < {1}99 '.format(x, y)
	sql = sql + 'ORDER BY c.time DESC LIMIT 20;'
	
	conquers = query(sql)
	msg.show('-'*40, 'black', True)
	msg.show('Conquers in Ocean {0}'.format(ocean), 'yellow', True)
	data = ''
	for conquer in conquers:
		
		if bb == True:
			# Date and time of the conquer
			data = data + '\n{0}: '.format(conquer[1])
			
			# Town ID/Name
			data = data + '[town]{0}[/town] '.format(conquer[0])
		
			# Points
			data = data + '({0}) '.format(conquer[6]) + '\n'
		
			# Old Owner
			old_player = get('players', conquer[3])
			try: data = data + '[player]{0}[/player]'.format(urllib.unquote(old_player[0][1]).replace('+', ' '))
			except: data = data + '[b][color=#666666]none[/color][/b]'
		
			# Old Alliance
			old_ally = get('alliances', conquer[5])
			try: data = data + ' ([ally]{0}[/ally])'.format(urllib.unquote(old_ally[0][1]).replace('+', ' '))
			except: data = data + ' [b][color=#666666](none)[/color][/b]'
		
			# Arrow
			data = data + ' [b][color=#FF0000]->[/color][/b] '
		
			# New Owner
			new_player = get('players', conquer[2])
			try: data = data + '[player]{0}[/player]'.format(urllib.unquote(new_player[0][1]).replace('+', ' '))
			except: data = data + '[b][color=#666666]none[/color][/b]'
		
			# New Alliance
			old_ally = get('alliances', conquer[4])
			try: data = data + ' ([ally]{0}[/ally])'.format(urllib.unquote(old_ally[0][1]).replace('+', ' ')) + '\n'
			except: data = data + ' [b][color=#666666](none)[/color][/b]\n'
			
		else:
			
			# Date and time of the conquer
			msg.show('{0}: '.format(conquer[1]), 'blue')
			
			# Town ID/Name
			msg.show('[{0}] '.format(conquer[0]), 'white')
			town = get('towns', conquer[0])
			msg.show('{0} '.format(urllib.unquote(town[0][2]).replace('+', ' ')), 'white')
		
			# Points
			msg.show('{0} '.format(conquer[6]), 'yellow')
		
			# Old Owner
			old_player = get('players', conquer[3])
			try: msg.show('{0}'.format(urllib.unquote(old_player[0][1]).replace('+', ' ')), 'green', bold=True)
			except: msg.show('none', 'black')
		
			# Old Alliance
			old_ally = get('alliances', conquer[5])
			try: msg.show(' ({0})'.format(urllib.unquote(old_ally[0][1]).replace('+', ' ')), 'green')
			except: msg.show(' (none)', 'black')
		
			# Arrow
			msg.show(' -> ', 'red', bold=True)
		
			# New Owner
			new_player = get('players', conquer[2])
			try: msg.show('{0}'.format(urllib.unquote(new_player[0][1]).replace('+', ' ')), 'cyan', bold=True)
			except: msg.show('none', 'black')
		
			# New Alliance
			old_ally = get('alliances', conquer[4])
			try: msg.show(' ({0})'.format(urllib.unquote(old_ally[0][1]).replace('+', ' ')), 'cyan', True)
			except: msg.show(' (none)', 'black', True)
			
	if bb == True:
		clipboard.set_text(data)
		msg.show('Data on the clipboard', 'blue', True)
	msg.show('-'*40, 'black', True)

